﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassLibrary.Data;
using ClassLibrary.Entity;
using System.Data;

namespace ClassLibrary.DAO
{
    public class TblAccountsDAO
    {
        private DBService _db = null;
        public TblAccountsDAO(string ConnectionString)
        {
            _db = new DBService(ConnectionString);
        }

        public bool InsUpdAccounts(TblAccounts Account, int mode)
        {
            bool flag = false;
            try
            {
                int result = 0;
                result = _db.ExeSP_NonQuery("sp_TblAccounts_InsUp",
                    new string[] { "@FirstName", "@LastName", "@EmailAcc", "@UsernameAcc", "@PasswordAcc", "@PhoneNumber", "@Country", "@TypeAcc", "@Display", "@Mode" },
                    new object[] { Account.FirstName, Account.LastName, Account.EmailAcc, Account.UsernameAcc, Account.PasswordAcc, Account.PhoneNumber, Account.Country, Account.TypeAcc, Account.Display,  mode},
                    CommandType.StoredProcedure);

                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        // kiem tra email

        public bool CheckExits(string username, int mode)
        {
            bool flag = false;
            try
            {
                DataTable dt = _db.ExeSP_DataTable("TblAccounts_CheckExists",
                    new string[] { "@UsernameAcc", "@Mode" },
                    new object[] { username, mode },
                    CommandType.StoredProcedure);
                if (dt.Rows.Count > 0)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        //Get user
        public TblAccounts GetUser(string emailaddress, string pass)
        {
            TblAccounts user = new TblAccounts();
            
            try
            {
                DataTable dt = _db.ExeSP_DataTable("sp_TblAccounts_Check",
                   new string[] { "@UsernameAcc", "@Password" },
                   new object[] { emailaddress, pass },
                  CommandType.StoredProcedure);

                foreach (DataRow dr in dt.Rows)
                {
                    user.EmailAcc = dr["EmailAcc"].ToString() ;
                    user.IDAcc = Convert.ToInt32(dr["IDAcc"]);
                    user.TypeAcc = Convert.ToInt32(dr["TypeAcc"]);
                    user.FirstName = dr["FirstName"].ToString();
                    user.LastName = dr["LastName"].ToString();
                    user.PasswordAcc = pass;
                    user.PhoneNumber = dr["PhoneNumber"].ToString();
                    user.Country = dr["Country"].ToString();
                    user.Display = Convert.ToBoolean(dr["Display"].ToString());
                    user.UsernameAcc = emailaddress;
                    user.DateRegister = Convert.ToDateTime(dr["DateRegister"]);
                    user.DateExpiry = Convert.ToDateTime(dr["DateExpiry"]);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
        }

        public TblAccounts GetUser_Check(string emailaddress)
        {
            TblAccounts user = new TblAccounts();

            try
            {
                DataTable dt = _db.ExeSP_DataTable("select * from TblAccounts where UsernameAcc = @emailaddress ",
                   new string[] { "@emailaddress" },
                   new object[] { emailaddress},
                  CommandType.Text);

                foreach (DataRow dr in dt.Rows)
                {
                    user.EmailAcc = dr["EmailAcc"].ToString();
                    user.IDAcc = Convert.ToInt32(dr["IDAcc"]);
                    user.TypeAcc = Convert.ToInt32(dr["TypeAcc"]);
                    user.FirstName = dr["FirstName"].ToString();
                    user.LastName = dr["LastName"].ToString();
                    user.PasswordAcc = dr["PasswordAcc"].ToString();
                    user.PhoneNumber = dr["PhoneNumber"].ToString();
                    user.Country = dr["Country"].ToString();
                    user.Display = Convert.ToBoolean(dr["Display"].ToString());
                    user.UsernameAcc = emailaddress;


                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
        }

        public TblAccounts GetAdmin(string UsernameAcc, string PassWord)
        {
            TblAccounts user = new TblAccounts();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("sp_TblUserAccount_AdminCheck",
                   new string[] { "@UsernameAcc", "@Password" },
                   new object[] { UsernameAcc, PassWord },
                  CommandType.StoredProcedure);

                foreach (DataRow dr in dt.Rows)
                {
                    user.EmailAcc = dr["EmailAcc"].ToString();
                    user.IDAcc = Convert.ToInt32(dr["IDAcc"]);
                    user.TypeAcc = Convert.ToInt32(dr["TypeAcc"]);
                    user.LastName = dr["LastName"].ToString();
                    user.FirstName = dr["FirstName"].ToString();
                    user.PasswordAcc = dr["PasswordAcc"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
        }

        public DataTable GetAllAccounts()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from TblAccounts where TypeAcc not in(1)", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetAllAccountsVIP()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from TblAccounts where TypeAcc not in (1, 2) and Display = 1", false, CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetAllAccountsWaitingVIP()
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("sp_GetListAccountsWaitingBecomeVIP", false, CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public TblAccounts GetAll(string useraccount)
        {
            TblAccounts user = new TblAccounts();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("select * from TblAccounts where UsernameAcc = @useraccount ",
                   new string[] { "@useraccount" },
                   new object[] { useraccount },
                  CommandType.Text);

                foreach (DataRow dr in dt.Rows)
                {
                    user.EmailAcc = dr["EmailAcc"].ToString();
                    user.UsernameAcc = dr["UsernameAcc"].ToString();
                    user.IDAcc = Convert.ToInt32(dr["IDAcc"]);
                    user.TypeAcc = Convert.ToInt32(dr["TypeAcc"]);
                    user.PhoneNumber = dr["PhoneNumber"].ToString();
                    user.LastName = dr["LastName"].ToString();
                    user.FirstName = dr["FirstName"].ToString();
                    user.PasswordAcc = dr["PasswordAcc"].ToString();
                    user.Country = dr["Country"].ToString();
                    user.Display = Convert.ToBoolean(dr["Display"].ToString());
                    user.EmailAcc = dr["EmailAcc"].ToString();

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
        }
        public TblAccounts GetAccountById(int IDAcc)
        {
            TblAccounts user = new TblAccounts();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("select * from TblAccounts where IDAcc = @IDAcc ",
                   new string[] { "@IDAcc" },
                   new object[] { IDAcc },
                  CommandType.Text);

                foreach (DataRow dr in dt.Rows)
                {
                    user.EmailAcc = dr["EmailAcc"].ToString();
                    user.UsernameAcc = dr["UsernameAcc"].ToString();
                    user.IDAcc = Convert.ToInt32(dr["IDAcc"]);
                    user.TypeAcc = Convert.ToInt32(dr["TypeAcc"]);
                    user.PhoneNumber = dr["PhoneNumber"].ToString();
                    user.LastName = dr["LastName"].ToString();
                    user.FirstName = dr["FirstName"].ToString();
                    user.PasswordAcc = dr["PasswordAcc"].ToString();
                    user.Country = dr["Country"].ToString();
                    user.Display = Convert.ToBoolean(dr["Display"].ToString());
                    user.DateRegister = Convert.ToDateTime(dr["DateRegister"]);
                    user.DateExpiry = Convert.ToDateTime(dr["DateExpiry"]);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return user;
        }

        public bool BecomeVIP(int IDAcc, int TypeAcc, Boolean Display, int DateQuantity, int mode)
        {
            bool flag = false;
            try 
            {
                _db.ExeSP_NonQuery("sp_BecomeVIP",
                    new string[] { "@IDAcc", "@TypeAcc", "@Display", "@DateQuantity", "@mode" },
                    new object[] { IDAcc, TypeAcc, Display, DateQuantity, mode},
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
